package com.cmnit.analysis.dao

import com.cmnit.analysis.common.TDao
import org.apache.log4j.Logger
import org.apache.spark.sql.DataFrame

class BroadcastDao extends TDao {

  private val logger: Logger = Logger.getLogger(this.getClass)

  /**
   * 广播收费站码表
   *
   * @return
   */
  def stationInfoBroadcast(): DataFrame = {
    sparkSQL(
      "SELECT " +
        "gbtollstationid, " +
        "tollstationname " +
        "FROM " +
        "dim.dim_tbl_gbsbstationbaseinfo "
    )
  }

  /**
   * 车牌-车辆类型码表
   */
  def vehicleBasicBroadcast(): DataFrame = {
    sparkSQL(
      "select " +
        "vehicleplate," +
        "vehicletype " +
        "from " +
        "dim.dim_tbl_vehiclebasicinfo")
  }

  /**
   * 广播门架信息表
   */
  def gantryInfoBroadcast(): DataFrame = {
    sparkSQL(
      "select " +
        "id," +
        "gantryname," +
        "gantryid," +
        "gantrytype," +
        "'江西省' as province " +
        "from " +
        "dim.dim_tbl_gantrybaseinfo")
  }

  /**
   * 广播门架中间表
   */
  def gantryBroadcast(): DataFrame = {
    sparkSQL(
      "select " +
        "stationid as tollstationCode," +
        "stationname as tollstationName," +
        "mjid as gantryId," +
        "mjname as gantryName," +
        "tollstation as tollstationProvince," +
        "roadid as roadId," +
        "roadname as roadName," +
        "'江西省' as province " +
        "from dim.dim_tb_station_mj_rela")
  }

  /**
   * 广播车道中间表
   */
  def laneBroadcast(): DataFrame = {
    sparkSQL(
      "select " +
        "p.ORGID as tollstationProvince," +
        "p.ORGNAME as tollstationName," +
        "m.ORGID as roadId," +
        "m.ORGNAME as roadName," +
        "'江西省' as province " +
        "from " +
        "(select " +
        "distinct " +
        "orgid," +
        "orgname," +
        "BL_ROAD " +
        "from " +
        "dim.dim_tbl_orgcodedic " +
        "where " +
        "ORGTYPE='6' " +
        "and lastver in (select max(lastver) " +
        "from dim.dim_tbl_orgcodedic)) p " +
        "LEFT JOIN " +
        "(select " +
        "distinct " +
        "orgid," +
        "orgname " +
        "from " +
        "dim.dim_tbl_orgcodedic " +
        "where ORGTYPE='8' " +
        "and lastver in (select max(lastver) " +
        "from dim.dim_tbl_orgcodedic)) m " +
        "on p.BL_ROAD = m.ORGID ")
  }
}
